<!---
Copyright: (c) 2006 The MachBlog Authors
Authors: Matt Woodward (mpwoodward@gmail.com) & Peter J. Farrell (pjf@maestropublishing.com)

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

$Id: $

Notes:
--->
<cfcomponent
	displayname="CommentGateway"
	output="false"
	extends="CommentGateway"
	hint="CommentGateway for MachBlog (PostgreSQL)">

	<cffunction name="init" access="public" output="false" returntype="CommentGateway"
			hint="Constructor for this CFC">
		<cfreturn this />
	</cffunction>

	<cffunction name="getComments" access="public" output="false" returntype="query"
			hint="Returns a query containing recent comments across all entries">
		<cfargument name="entryId" type="string" required="false" default="" />
		<cfargument name="numToGet" type="numeric" required="false" default="-1" />
		<cfargument name="includeInactive" type="boolean" required="false" default="false" />
		<cfargument name="searchTerms" type="string" required="false" default="" />
		<cfargument name="sortOrder" type="string" required="false" default="ASC" />
		
		<cfset var getComments = "" />
		<cfset var getBlockedIps = "" />

		<cftry>
			<cfquery name="getComments" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#" maxrows="#arguments.numToGet#">
				SELECT 	machblog_comment.*,
						machblog_entry.entry_id,
						machblog_entry.title
				FROM 	machblog_comment
				LEFT OUTER JOIN machblog_entry ON machblog_comment.entry_id = machblog_entry.entry_id
				WHERE 	(subscription_only = 0 OR subscription_only IS NULL)
				<cfif arguments.entryId NEQ "">
					AND machblog_comment.entry_id = <cfqueryparam value="#arguments.entryId#"
																	cfsqltype="cf_sql_char"
																	maxlength="35" />
				</cfif>
				<cfif Not arguments.includeInactive>
					AND machblog_comment.is_active =
												<cfqueryparam value="1" cfsqltype="cf_sql_smallint" />
				</cfif>
				<cfif arguments.searchTerms NEQ "">
					AND  (machblog_comment.comment LIKE '%#arguments.searchTerms#%'
							OR machblog_comment.name LIKE '%#arguments.searchTerms#%'
							OR machblog_comment.email LIKE '%#arguments.searchTerms#%'
							OR machblog_comment.url LIKE '%#arguments.searchTerms#%')
				</cfif>
				ORDER BY machblog_comment.dt_created #arguments.sortOrder#
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfset QueryAddColumn(getComments, "ip_is_blocked", ArrayNew(1)) />

		<cfif IsQuery(getComments) AND getComments.RecordCount GT 0>
			<cftry>
				<cfquery name="getBlockedIps" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
					SELECT blocked_ip
					FROM machblog_blocked_ip
					WHERE blocked_ip IN (#QuotedValueList(getComments.ip_created)#)
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error [databaseerror]" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>

		<cfloop query="getComments">
			<cfif IsQuery(getBlockedIps) AND getBlockedIps.RecordCount GT 0>
				<cfif ListFind(ValueList(getBlockedIps.blocked_ip), getComments.ip_created)>
					<cfset QuerySetCell(getComments, "ip_is_blocked", 1, getComments.CurrentRow) />
				</cfif>

				<cfelse>
					<cfset QuerySetCell(getComments, "ip_is_blocked", 0, getComments.CurrentRow) />
			</cfif>
		</cfloop>

		<cfreturn getComments />
	</cffunction>

	<cffunction name="getCommentCount" access="public" output="false" returntype="numeric"
			hint="Returns the number of comments, optionally for a specific date.">
		<cfargument name="commentDate" type="date" required="false" default="#CreateDate(1900, 1, 1)#" />

		<cfset var getCommentCount = "" />
		<cfset var commentCount = 0 />
		<cfset var startDate = CreateDateTime(DatePart("yyyy", arguments.commentDate),
												DatePart("m", arguments.commentDate),
												DatePart("d", arguments.commentDate),
												00, 00, 00) />
		<cfset var endDate = CreateDateTime(DatePart("yyyy", arguments.commentDate),
												DatePart("m", arguments.commentDate),
												DatePart("d", arguments.commentDate),
												23, 59, 59) />
		
		<!--- set start and end dates to epochs --->
		<cfset startDate = getResourceBundleFacade().getLocaleUtils().toEpoch(startDate) />
		<cfset endDate = getResourceBundleFacade().getLocaleUtils().toEpoch(endDate) />
		
		<cftry>
			<cfquery name="getCommentCount" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				SELECT COUNT(comment_id) AS comment_count
				FROM machblog_comment
				WHERE (subscription_only = <cfqueryparam value="0" cfsqltype="cf_sql_smallint" />
						OR subscription_only IS NULL)
				<cfif arguments.commentDate NEQ CreateDate(1900, 1, 1)>
					AND dt_created >= <cfqueryparam value="#startDate#" cfsqltype="cf_sql_bigint" />
					AND dt_created <= <cfqueryparam value="#endDate#" cfsqltype="cf_sql_bigint" />
				</cfif>
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfif IsQuery(getCommentCount) AND getCommentCount.RecordCount GT 0>
			<cfset commentCount = getCommentCount.comment_count />
		</cfif>

		<cfreturn commentCount />
	</cffunction>

	<cffunction name="getSubscriptionCount" access="public" output="false" returntype="numeric"
			hint="Returns the total number of comment subscriptions across the entire blog">
		<cfset var getSubscriptionCount = "" />
		<cfset subscriptionCount = 0 />

		<cftry>
			<cfquery name="getSubscriptionCount" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				SELECT COUNT(comment_id) AS subscription_count
				FROM machblog_comment
				WHERE is_subscribed = <cfqueryparam value="1" cfsqltype="cf_sql_smallint" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error [databaseerror]" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfif IsQuery(getSubscriptionCount) AND getSubscriptionCount.RecordCount GT 0>
			<cfset subscriptionCount = getSubscriptionCount.subscription_count />
		</cfif>

		<cfreturn subscriptionCount />
	</cffunction>

	<cffunction name="deactivateCommentsByEntryId" access="public" output="false" returntype="void"
			hint="Deactivates all comments for a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />

		<cfset var updateComments = "" />

		<cftry>
			<cfquery name="updateComments" datasource="#getDatasource().getDsn()#"
						username="#getDatasource.getUserName()#"
						password="#getDatasource().getPassword()#">
				UPDATE machblog_comment
				SET is_active = <cfqueryparam value="0" cfsqltype="cf_sql_smallint" />
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char"
											maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="activateCommentsByEntryId" access="public" output="false" returntype="void"
			hint="Activates all comments for a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />

		<cfset var updateComments = "" />

		<cftry>
			<cfquery name="updateComments" datasource="#getDatasource().getDsn()#"
						username="#getDatasource.getUserName()#"
						password="#getDatasource().getPassword()#">
				UPDATE machblog_comment
				SET is_active = <cfqueryparam value="1" cfsqltype="cf_sql_smallint" />
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char"
											maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="deleteCommentsByEntryId" access="public" output="false" returntype="void"
			hint="Deletes all comments associated with a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />

		<cfset var deleteComments = "" />

		<cftry>
			<cfquery name="deleteComments" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				DELETE FROM machblog_comment
				WHERE entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char"
											maxlength="35" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="toggleCommentDisplay" access="public" output="false" returntype="void" hint="Approves or unapproves a comment">
		<cfargument name="commentId" type="uuid" required="true" />
		<cfargument name="adminId" type="uuid" required="true" />
		<cfargument name="action" type="string" required="true" />

		<cfset var toggleComment = "" />
		<cfset var active = 0 />

		<cfif arguments.action EQ "show">
			<cfset active = 1 />
		</cfif>

		<cftry>
			<cfquery name="toggleComment" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				UPDATE 	machblog_comment
				SET 	is_active = <cfqueryparam value="#active#" cfsqltype="cf_sql_smallint" />,
						modified_by_id = <cfqueryparam value="#arguments.adminId#" cfsqltype="cf_sql_char" maxlength="35" />,
						dt_modified = <cfqueryparam value="#Now()#" cfsqltype="cf_sql_bigint" />,
						ip_modified = <cfqueryparam value="#CGI.REMOTE_ADDR#"
												cfsqltype="cf_sql_varchar" maxlength="15" />
				WHERE 	comment_id = <cfqueryparam value="#arguments.commentId#" cfsqltype="cf_sql_char" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="toggleIpBlock" access="public" output="false" returntype="void"
			hint="Adds or removes an IP to/from the block list">
		<cfargument name="ipAddress" type="string" required="true" />
		<cfargument name="action" type="string" required="true" />
		<cfargument name="adminId" type="uuid" required="true" />

		<cfset var insertIp = "" />
		<cfset var deleteIp = "" />

		<cfif arguments.action EQ "block">
			<cftry>
				<cfquery name="insertIp" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
					INSERT INTO machblog_blocked_ip (
						blocked_ip,
						created_by_id,
						dt_created,
						ip_created
					) VALUES (
						<cfqueryparam value="#arguments.ipAddress#" cfsqltype="cf_sql_varchar"
										maxlength="15" />,
						<cfqueryparam value="#arguments.adminId#"
										cfsqltype="cf_sql_char" maxlength="35" />,
						<cfqueryparam value="#Now()#" cfsqltype="cf_sql_bigint" />,
						<cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar" maxlength="15" />
					)
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>

		<cfelseif arguments.action EQ "unblock">
			<cftry>
				<cfquery name="deleteIp" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
					DELETE FROM machblog_blocked_ip
					WHERE blocked_ip = <cfqueryparam value="#arguments.ipAddress#"
											cfsqltype="cf_sql_varchar" maxlength="15" />
				</cfquery>
				<cfcatch type="database">
					<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
				</cfcatch>
			</cftry>
		</cfif>
	</cffunction>

	<cffunction name="updateEmailSentFlag" access="public" output="false" returntype="void"
			hint="Updates the subscriber email sent flag for a specific comment">
		<cfargument name="commentId" type="uuid" required="true" />

		<cfset var updateComment = "" />

		<cftry>
			<cfquery name="updateComment" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				UPDATE machblog_comment
				SET subscriber_email_sent = <cfqueryparam value="1" cfsqltype="cf_sql_smallint" />
				WHERE comment_id = <cfqueryparam value="#arguments.commentId#" cfsqltype="cf_sql_char" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="deleteComments" access="public" output="false" returntype="void" hint="Deletes comments">
		<cfargument name="deleteIds" type="string" required="true" />

		<cfset var deleteComments = "" />

		<cftry>
			<cfquery name="deleteComments" datasource="#getDatasource().getDsn()#"
					username="#getDatasource().getUserName()#"
					password="#getDatasource().getPassword()#">
				DELETE FROM machblog_comment
				WHERE comment_id IN (<cfqueryparam value="#arguments.deleteIds#" list="true" />)
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>

	<cffunction name="getCommentSubscribers" access="public" output="false" returntype="query"
			hint="Returns a query containing email addresses for people subscribed to comments for a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />

		<cfset var getSubscribers = QueryNew("email") />

		<cftry>
			<cfquery name="getSubscribers" datasource="#getDatasource().getDsn()#"
						username="#getDatasource().getUserName()#"
						password="#getDatasource().getPassword()#">
				SELECT 	DISTINCT(email) 
				FROM 	machblog_comment 
				WHERE 	entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" 
													maxlength="35" /> 
				AND 	is_subscribed = <cfqueryparam value="1" cfsqltype="cf_sql_smallint" />
				AND 	email IS NOT NULL 
				ORDER BY email ASC
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>

		<cfreturn getSubscribers />
	</cffunction>

	<cffunction name="unsubscribe" access="public" output="false" returntype="void" 
			hint="Unsubscribes an email address from the comments for a particular entry">
		<cfargument name="entryId" type="uuid" required="true" />
		<cfargument name="email" type="string" required="true" />
		
		<cfset var updateComments = 0 />
		
		<cftry>
			<cfquery name="updateComments" datasource="#getDatasource().getDsn()#" 
					username="#getDatasource().getUserName()#" 
					password="#getDatasource().getPassword()#">
				UPDATE 	machblog_comment 
				SET 	is_subscribed = <cfqueryparam value="0" cfsqltype="cf_sql_smallint" /> 
				WHERE 	entry_id = <cfqueryparam value="#arguments.entryId#" cfsqltype="cf_sql_char" maxlength="35" /> 
				AND 	email = <cfqueryparam value="#arguments.email#" cfsqltype="cf_sql_varchar" maxlength="100" />
			</cfquery>
			<cfcatch type="database">
				<cfthrow type="application" message="Database Error" detail="#CFCATCH.Detail#" />
			</cfcatch>
		</cftry>
	</cffunction>
</cfcomponent>
